package nl.ViewerServer.Database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import nl.ViewerServer.Application;
import nl.ViewerServer.ServerManager.ServerManager;
import nl.ViewerServer.ServerManager.DataManager.Objects.Modality;
import nl.ViewerServer.ServerManager.DataManager.Objects.Visualization;

import org.apache.log4j.Logger;

public class DatabaseConnection {
   protected static Logger     log = Logger.getLogger( DatabaseConnection.class );
   protected Connection        conn;

   protected PreparedStatement getVisualizationsAndOutputs;
   protected PreparedStatement getModalityVisualizations;
   protected PreparedStatement getDatasets;
   protected PreparedStatement getModalityAlignments;

   public DatabaseConnection() throws ClassNotFoundException, SQLException {
      String connectionUrl = "jdbc:mysql://" + Application.config.databaseHost + ":" + Application.config.databasePort + "/" + Application.config.databaseName + "?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=100&repStmtCacheSqlLimit=100";

      // Get the JDBC driver
      try {
         Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
      }
      catch( Exception e ) {
         throw new SQLException( e.getMessage() );
      }
      // Connect
      conn = DriverManager.getConnection( connectionUrl, Application.config.databaseUser,
            Application.config.databasePassword );

      prepareStatements();

      log.info( "Database initialized (" + connectionUrl + ")" );
   }

   public void close() {
      try {
         conn.close();
      }
      catch( SQLException e ) {
         // ignore
      }
   }

   public synchronized void initialize() throws SQLException {
      loadDatasets();
      loadVisualizations();
   }

   protected void prepareStatements() throws SQLException {
      getVisualizationsAndOutputs = conn.prepareStatement( "SELECT * FROM `pipelineVisualization` v, `pipelineOutput` o, `visualization_output` vo WHERE v.`visualizationId` = vo.`visualizationId` AND vo.`outputId` = o.`outputId`" );
      getModalityVisualizations = conn.prepareStatement( "SELECT * FROM `modality` m, `modality_visualization` mv, `pipelineVisualization` v WHERE m.`modalityId` = mv.`modalityId` AND mv.`visualizationId` = v.`visualizationId`" );
      getDatasets = conn.prepareStatement( "SELECT * FROM `dataset` d, `modality` m WHERE d.`datasetId` = m.`datasetId`" );
      getModalityAlignments = conn.prepareStatement( "SELECT * FROM `modality_aligned_to_modality`" );
   }

   private void loadDatasets() throws SQLException {
      // find all datasets and modalities and for each modality its pipeline source
      ResultSet rs = getDatasets.executeQuery();
      while( rs.next() ) {
         /* Modality m = */ServerManager.dataManager.addModality( rs.getInt( "d.datasetId" ),
               rs.getString( "d.name" ), rs.getString( "d.description" ), rs.getString( "d.ACL" ),
               rs.getInt( "m.modalityId" ), rs.getString( "m.name" ),
               rs.getString( "m.description" ), rs.getInt( "m.sourceId" ), rs.getString( "m.ACL" ) );

         log.info( "Added dataset " + rs.getString( "d.name" ) + " with modality " + rs.getString( "m.name" ) + " with ACL " + rs.getString( "m.ACL" ) );

      }
      rs.close();

      rs = getModalityAlignments.executeQuery();
      while( rs.next() ) {
         if( ServerManager.dataManager.addModalityAlignment( rs.getInt( 1 ), rs.getInt( 2 ) ) ) {
            log.info( "Added dataset alignment between modalities " + rs.getInt( 1 ) + " and " + rs.getInt( 2 ) );
         }
      }
      rs.close();

      log.debug( "datasets imported: " + ServerManager.dataManager.getDatasets().size() );
   }

   private void loadVisualizations() throws SQLException {
      // find all visualizations and outputs for all visualizations for all modalities
      ResultSet rs = getVisualizationsAndOutputs.executeQuery();

      while( rs.next() ) {
         ServerManager.dataManager.addVisualizationAndOutputs( rs.getInt( "v.visualizationId" ),
               rs.getString( "v.name" ), rs.getString( "v.visualizationType" ), rs.getString( "v.ACL" ),
               rs.getBoolean( "requiresAlignedSources" ), rs.getInt( "o.outputId" ), rs.getString( "o.name" ),
               rs.getString( "o.outputDataType" ) );

         log.info( "Added visualization " + rs.getString( "v.name" ) + ", output " + rs.getString( "o.name" ) );

      }
      rs.close();

      rs = getModalityVisualizations.executeQuery();
      while( rs.next() ) {
         int visualizationId = rs.getInt( "v.visualizationId" );
         int modalityId = rs.getInt( "m.modalityId" );

         Visualization v = ServerManager.dataManager.getVisualization( visualizationId );
         if( v == null ) {
            log.warn( "Visualization " + visualizationId + " could not be found in modality_visualization with modality " + modalityId );
         } else {
            Modality m = ServerManager.dataManager.getModality( modalityId );
            if( m == null ) {
               log.warn( "Modality " + modalityId + " could not be found in modality_visualization with visualization " + visualizationId );
            } else {
               log.info( "Added visualization " + v.name + " to modality " + m );

               m.addVisualization( v );
            }
         }
      }
   }



}